package es.cazadoresmadridejos.beans.dao.partner.impl;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.displaytag.pagination.PaginatedList;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.transform.AliasToBeanResultTransformer;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;

import es.cazadoresmadridejos.beans.dao.partner.PartnerDao;
import es.cazadoresmadridejos.common.bean.dao.impl.DaoImpl;
import es.cazadoresmadridejos.model.BigGame;
import es.cazadoresmadridejos.model.Partner;
import es.cazadoresmadridejos.model.BigGame.BigGameStatus;
import es.cazadoresmadridejos.model.Partner.Role;
import es.cazadoresmadridejos.objs.criteria.PartnerCriteria;
import es.cazadoresmadridejos.objs.tablrecords.BigGameOrderTableRecord;
import es.cazadoresmadridejos.objs.web.Page;
import es.cazadoresmadridejos.objs.web.PaginatedListImpl;

@SuppressWarnings("unchecked")
@Repository("partnerDao")
public class PartnerDaoImpl extends DaoImpl<Partner> implements PartnerDao{

	@Override
	public Partner newInstance() {
		return new Partner();
	}

	@Override
	public Partner getByName(final String userName) {
		return (Partner) getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
			SQLException {

				StringBuilder hql = new StringBuilder();
				hql
				.append("select p ")
				.append(" from Partner p ")
				.append(" where p.userid = :userid ");

				Query query = session.createQuery(hql.toString());

				query.setString("userid", userName);
				return query.uniqueResult();
			}
		});
	}

	@Override
	public PaginatedList findAllPaginated(final Page page) {
		return (PaginatedList) getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {

				Map<String,Object> params = new HashMap<String, Object>();
				StringBuilder hql = new StringBuilder();
				StringBuilder hqlCount = new StringBuilder();
				hql
				.append("select distinct(p) ")
				.append(" from Partner p ")
				.append(" where 1=1 ");
				hqlCount
				.append("select count(p) ")
				.append(" from Partner p ")
				.append(" where 1=1 ")
				.append(" 	and p.role != 'ADMINISTRADOR' ")
				.append(" ");

				PartnerCriteria cr = (PartnerCriteria) page.getContainer();
				if(cr != null){
					if(cr.getActive() != null){
						hqlList(hql, hqlCount, " and p.active = :active ");
						params.put("active", cr.getActive());
					}

					if(StringUtils.isNotBlank(cr.getDni())){
						hqlList(hql, hqlCount, " and p.nationalIdentityNumber like (:dni) ");
						params.put("dni", cr.getDni());
					}
					if(StringUtils.isNotBlank(cr.getLastName())){
						hqlList(hql, hqlCount, " and p.lastName like (:lastName) ");
						params.put("lastName", cr.getLastName());
					}
					if(cr.getId() != null){
						hqlList(hql, hqlCount, " and p.id = (:id) ");
						params.put("id", cr.getId());
					}
					
					if(StringUtils.isNotBlank(cr.getUserid())){
						hqlList(hql, hqlCount, " and p.userid like (:userid) ");
						params.put("userid", cr.getUserid());
					}
					if(cr.getType() != null){
						hqlList(hql, hqlCount, " and p.hunterType= (:type) ");
						params.put("type", cr.getType());
					}
				}

				hql.append(getSort(page,"p"));

				Query query = session.createQuery(hql.toString());
				Query queryCount = session.createQuery(hqlCount.toString());

				query.setProperties(params);
				queryCount.setProperties(params);

				query.setMaxResults(page.getLimit());
				query.setFirstResult(page.getLimit() * (page.getPage() - 1));

				return new PaginatedListImpl<Partner>(query.list(), 
						page, 
						((Long)queryCount.uniqueResult()).intValue());
			}
		});
	}

	@Override
	public List<Partner> findAllForAutocomplete() {
		return (List<Partner>) getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {

				StringBuilder hql = new StringBuilder();
				hql.append("select distinct p.id as id, p.name as name,p.lastName as lastName ")
				.append(" from Partner p ")
				.append(" where ").append(" p.role != 'ADMINISTRADOR' ");

				Query query = session.createQuery(hql.toString());

				query.setResultTransformer(new AliasToBeanResultTransformer(Partner.class));

				return query.list();
			}
		});
	}

	@Override
	public List<Partner> findAllForAutocompleteBigGame(final Long notIn) {
		return (List<Partner>) getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {

				StringBuilder hql = new StringBuilder();
				hql.append("select distinct p.id as id, p.name as name,p.lastName as lastName ")
				.append(" from Partner p ")
				.append(" where p.active = 1")
				.append(" 	and p.hasBigGame is true ")
				.append(" 	and p.onlyManteinance is not true ")
				.append(" 	and p.role != 'ADMINISTRADOR' ");
				if(notIn != null){
					hql.append(" 	and p.id != :id ");
				}

				Query query = session.createQuery(hql.toString());

				query.setResultTransformer(new AliasToBeanResultTransformer(Partner.class));

				if(notIn != null){
					query.setLong("id", notIn);
				}

				return query.list();
			}
		});
	}

	@Override
	public List<Partner> findAllByBigGame(final Long bigGameId) {
		return (List<Partner>) getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {

				StringBuilder hql = new StringBuilder();
				hql
				.append("select bg.partners ")
				.append("from BigGame bg ")
				.append(" where bg.id = :bigGameId ")
				.append(" 	and p.role != 'ADMINISTRADOR' ")
				.append("");

				Query query = session.createQuery(hql.toString());

				query.setLong("bigGameId", bigGameId);

				return query.list();
			}
		});
	}

	@Override
	public List<Partner> findAllByOrder(final boolean hasBigGame, final boolean hasDeer) {
		return (List<Partner>)getHibernateTemplate().execute(new HibernateCallback(){

			public Object doInHibernate(Session session){
				StringBuilder hql = new StringBuilder();
				hql
				.append(" select distinct p ")
				.append(" from Partner p ")
				.append(" where p.active = 1 ")
				.append(" 	and p.onlyManteinance is not true ")
				.append(" 	and p.role != 'ADMINISTRADOR' ");
				if(hasBigGame){
					hql.append(" and p.hasBigGame is true ");
					hql.append(" order by p.bigGameOrder asc ");
				}
				if(hasDeer){
					hql.append(" and p.hasDeer is true ");
					hql.append(" order by p.deerOrder asc ");
				}

				Query query = session.createQuery(hql.toString());

				return query.list();
			}
		});
	}

	//En PartnerDao
	public List<Partner> findNext(final Integer order,final Integer stands,final boolean hasBigGame, final boolean hasDeer){
		return (List<Partner>)getHibernateTemplate().execute(new HibernateCallback(){

			public Object doInHibernate(Session session){
				StringBuilder hql = new StringBuilder();
				hql
				.append(" select distinct p ")
				.append(" from Partner p ")
				.append(" where ")
				.append("     p.order > :order ")
				.append(" 	and p.onlyManteinance is not true ")
				.append(" 	and p.role != 'ADMINISTRADOR' ");

				if(hasBigGame){
					hql.append(" and p.hasBigGame is true ");
				}
				if(hasDeer){
					hql.append(" and p.hasDeer is true ");
				}	
				hql
				.append(" order by p.order asc ")
				.append("  ")
				.append(" ");

				Query query = session.createQuery(hql.toString());

				query.setMaxResults(stands);

				query.setParameter("order", order);

				return query.list();
			}
		});
	}

	@Override
	public List<Partner> findPartersFromOrderToBigGame(final Integer numeric, final boolean hasBigGame, 
			final Set<Long> partnersMap, final List<Role> roles) {
		return (List<Partner>) getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {

				StringBuilder hql = new StringBuilder();
				StringBuilder hql1 = new StringBuilder();
				StringBuilder hql12 = new StringBuilder();
				StringBuilder hql2 = new StringBuilder();
				hql
				.append("select distinct p ")
				.append(" from Partner p ")
				.append(" where ")
				.append(" 	p.role in (:roles) ")
				.append(" 	and p.onlyManteinance is not true ")
				.append(" 	and p.active = 1 ");
				if(CollectionUtils.isNotEmpty(partnersMap)){
					hql.append(" 	and p.id not in (:partners) ");
				}

				hql1.append("	and p.bigGameOrder > :order ");
				hql12.append("	and p.bigGameOrder <= :order ");

				if(hasBigGame){
					hql2.append(" and p.hasBigGame is true ");
				}

				hql2
				.append(" order by p.bigGameOrder asc ");

				Map<String,Object> params = new HashMap<String, Object>();
				params.put("order", numeric);
				params.put("roles", roles);

				if(CollectionUtils.isNotEmpty(partnersMap)){
					params.put("partners", partnersMap);
				}

				Query query = session.createQuery(hql.toString() + hql1.toString() + hql2.toString());
				query.setProperties(params);

				Query query2 = session.createQuery(hql.toString() + hql12.toString() + hql2.toString());
				query2.setProperties(params);



				List<Partner> l1 = query.list();
				List<Partner> l2 = query2.list();
				l1.addAll(l2);

				return l1;
			}
		});
	}

	@Override
	public List<BigGameOrderTableRecord> findAllByOrder() {
		return (List<BigGameOrderTableRecord>) getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				StringBuilder hqlLastClosedBigGame = new StringBuilder();
				hqlLastClosedBigGame
				.append(" select bigGame  ")
				.append(" from BigGame bigGame ")
				.append(" where bigGame.status = 'CERRADA' ")
				.append(" order by bigGame.date desc ");

				Query queryLastClsedBigGame = session.createQuery(hqlLastClosedBigGame.toString());

				List<BigGame> bgs = queryLastClsedBigGame.list();
				BigGame bigGame = null;
				if(!bgs.isEmpty()){
					bigGame = bgs.iterator().next();
				}

				StringBuilder hql = new StringBuilder();
				hql
				.append(" select p.name || ' ' || p.lastName as name,  ")
				.append(" p.id as partnerId,  ")
				.append(" p2.id as partnerPartnerId,  ")
				.append(" p.bigGameOrder as bigGameOrder,  ")

				.append(" (case when p.role = 'JUNTA' then true else false end) as isJunta, ")
				.append(" (case when p.id in ")
				.append(" 	(select partner.id from BigGameAssistance bga inner join bga.partner partner ")
				.append(" 	where bga.bigGame.status = :statusInProgress) ")
				.append("	then true else false end) as inNextBigGame, ")
				.append(" ");
				if(bigGame!= null){
					hql
					.append(" (case when p.id in ")
					.append(" 	(select partner.id from BigGameAssistance bga inner join bga.partner partner ")
					.append(" 	where  bga.bigGame.id = :lastBigGameClosedId) ")
					.append("	then true else false end) as inLastClosedBigGame, ")
					.append(" ")
					.append(" (case when p.id in ")
					.append(" 	(select partnerPartner.id from BigGameAssistance bga inner join bga.partner partner inner join partner.partner partnerPartner ")
					.append(" 	where bga.bigGame.id = :lastBigGameClosedId) ")
					.append("	then true else false end) as partnerInLastBigGame, ");
				}
				hql.append(" (case when p.id in ")
				.append(" 	(select partnerPartner.id from BigGameAssistance bga inner join bga.partner partner inner join partner.partner partnerPartner ")
				.append(" 	where bga.bigGame.status = :statusInProgress) ")
				.append("	then true else false end) as partnerInNextBigGame ")
				.append(" ")
				.append(" from Partner p ")
				.append(" 	left join p.partner p2 ")
				.append(" where p.active = 1 ")
				.append(" 	and p.role != 'ADMINISTRADOR' ")
				.append(" and p.hasBigGame is true ")
				.append(" order by p.bigGameOrder asc ");

				Query query = session.createQuery(hql.toString());

				query.setParameter("statusInProgress", BigGameStatus.EN_PROCESO);
				
				if(bigGame!= null){
					query.setLong("lastBigGameClosedId", bigGame.getId());
				}
				
				query.setResultTransformer(new AliasToBeanResultTransformer(BigGameOrderTableRecord.class));

				return query.list();
			}
		});
	}

	@Override
	public Integer getCountOfBigGameAssistances() {
		return (Integer) getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {

				StringBuilder hql = new StringBuilder();
				hql
					.append("select count(p) ")
					.append(" from Partner p ")
					.append(" where p.role != 'ADMINISTRADOR' ")
					.append(" 	and p.onlyManteinance is not true ")
					.append("	and p.active = 1 and p.hasBigGame is true");

				Query query = session.createQuery(hql.toString());
				
				return ((Long)query.uniqueResult()).intValue();
			}
		});
	}

}
